#coding=utf-8
import zipfile
from openpyxl.styles import PatternFill,Font
import yaml,xlrd
import smtplib

from email.mime.text import MIMEText
#from email.mime.application import MIMEApplication
from email.mime.multipart import MIMEMultipart
from email.header import Header
from log.log import Logger
import openpyxl
import logging.config


#连接mysql
class ConnectMysql:
    def __init__(self):
        import mysql.connector
        from config.config import host_mysql, user_mysql, password_mysql, database_mysql
        self.conn = mysql.connector.connect(
            host=host_mysql,  # localhost
            user=user_mysql,  # root
            password=password_mysql,  # wxk111
            database=database_mysql,  # first
            # port=3306
        )
        self.cur = self.conn.cursor()
    def select(self,sql):
        '''
        查询方法
        :param sql: 查询语句
        :return: 返回查询到的所有数据
        '''
        self.cur.execute("{}".format(sql))
        datas = self.cur.fetchall()
        return datas
    def update(self,sql):
        self.cur.execute("{}".format(sql))
        self.conn.commit()
        '''
        先关闭游标再关闭连接
        self.cur.close()
        self.conn.close()
        '''




#连接oracle
class ConnectOracle:
    def __init__(self):
        # 需要匹配版本，切换到合适的python版本就会有
        import cx_Oracle
        from config.config import name_oracle, password_oracle, ip_oracle, port_oracle, serverName_oracle
        # conn = cx_Oracle.connect('name/password@ip:port/serverName')      # 连接数据库
        self.conn = cx_Oracle.connect('{}/{}@{}:{}/{}'.format(name_oracle, password_oracle, ip_oracle, port_oracle, serverName_oracle))
        self.cur = self.conn.cursor()                                     # 获取cursor游标

    def select(self, sql):
        self.cur.execute(sql)
        datas = self.cur.fetchall()
        return datas

    def update(self, sql):
        self.cur.execute("{}".format(sql))
        self.conn.commit()
        '''
        先关闭游标再关闭连接
        self.cur.close()
        self.conn.close()
        '''

#连接sqlserver
class ConnectSqlserver:
    def __init__(self):
        import pymssql
        from config.config import name_sqlserver,password_sqlserver,ip_sqlserver,port_sqlserver,database_sqlserver
        self.conn = pymssql.connect('{}:{}', '{}', '{}', '{}'.format(ip_sqlserver,port_sqlserver,name_sqlserver,password_sqlserver,database_sqlserver)) #服务器名,账户,密码,数据库名)
        self.cur = self.conn.cursor()                                     # 获取cursor游标

    def select(self, sql):
        self.cur.execute(sql)
        datas = self.cur.fetchall()
        return datas

    def update(self, sql):
        self.cur.execute("{}".format(sql))
        self.conn.commit()
        '''
        先关闭游标再关闭连接
        self.cur.close()
        self.conn.close()
        '''

#连接greenplum
class ConnectGreenplum:
    def __init__(self):
        # 这类数据库选择表时必须加模式,比如：update gz_ods_schema.test_2 set id=8 where id=1
        # 也可以是python3-postgresql，参考：https://www.cnblogs.com/jmlovepython/p/5023794.html
        import psycopg2
        from config.config import database_greenplum,user_greenplum,password_greenplum,host_greenplum,port_greenplum
        self.conn = psycopg2.connect(database=database_greenplum, user=user_greenplum, password=password_greenplum, host=host_greenplum, port=port_greenplum)
        self.cur = self.conn.cursor()                                     # 获取cursor游标

    def select(self, sql):
        self.cur.execute(sql)
        datas = self.cur.fetchall()
        return datas

    def update(self, sql):
        self.cur.execute("{}".format(sql))
        self.conn.commit()
        '''
        先关闭游标再关闭连接
        self.cur.close()
        self.conn.close()
        '''

#连接postgresql
class ConnectPostgresql:
    def __init__(self):
        # 这类数据库选择表时必须加模式,比如：update gz_ods_schema.test_2 set id=8 where id=1
        # 也可以是python3-postgresql，参考：https://www.cnblogs.com/jmlovepython/p/5023794.html
        import psycopg2
        from config.config import database_postgresql,user_postgresql,password_postgresql,host_postgresql,port_postgresql
        self.conn = psycopg2.connect(database=database_postgresql, user=user_postgresql, password=password_postgresql, host=host_postgresql, port=port_postgresql)
        self.cur = self.conn.cursor()                                     # 获取cursor游标

    def select(self, sql):
        self.cur.execute(sql)
        datas = self.cur.fetchall()
        return datas

    def update(self, sql):
        self.cur.execute("{}".format(sql))
        self.conn.commit()
        '''
        先关闭游标再关闭连接
        self.cur.close()
        self.conn.close()
        '''

#连接达梦
class ConnectDM:
    def __init__(self):
        # dmPython要求DM Server版本大于7.0.0.9；Python版本大于2.6
        # 参考：https://blog.csdn.net/xk_xx/article/details/122850966  https://blog.csdn.net/qq_42079557/article/details/121938992
        '''
        安装方式：
        1.安装达梦数据库和python（已安装）
        2.配置达梦数据库和Python环境变量（已配置）
        3.配置dpi环境变量${DM_HOME}/drivers/dpi（否则会找不到dmPython包)
        4.进入${DM_HOME}/drivers/python/dmPython目录，使用管理员执行python setup.py install(这步是编译安装dmPyython)
        :return:
        '''
        import dmPython
        from config.config import user_dm,password_dm,server_dm,port_dm
        # connect = dmPython.connect(user="gpadmin", password="gpadmin", server="222.186.32.112", port="24532", autoCommit=True)
        r"""
        默认开启自动提交(autoCommit)模式
        """
        self.conn = dmPython.connect(user=user_dm, password=password_dm, server=server_dm,
                                         port=port_dm)  # user账号(对应模式名)
        self.cur = self.conn.cursor()                                     # 获取cursor游标

    def select(self, sql):
        self.cur.execute(sql)
        datas = self.cur.fetchall()
        print(datas)
        return datas

    def update(self, sql):
        self.cur.execute("{}".format(sql))
        self.conn.commit()
        '''
        先关闭游标再关闭连接
        self.cur.close()
        self.conn.close()
        '''
if __name__ == '__main__':
    ConnectDM().select('select * from TABLE_1')